package com.cpic.agencysurvey.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.sql.*;

/**
 * @author eason.yishen.zhou
 * 
 */
public class ProcedureServlet extends HttpServlet {
	private static final long serialVersionUID = 4027606939078022838L;

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {

		String procedure = req.getParameter("procedure");
		String id = req.getParameter("id");
		String name = req.getParameter("name");
		String phone = req.getParameter("phone");
		String sex = req.getParameter("sex");
		String birth_date = req.getParameter("birth_date");
		String empno = req.getParameter("empno");
		String recommend_name = req.getParameter("recommend_name");;
		String recommend_no = req.getParameter("recommend_no");
		String question_str = req.getParameter("question_str");
		String version = req.getParameter("version");

		if (procedure == null
				|| procedure.trim().equals("pro_ea_get_person_info") == false) {
			resp.getWriter().write("存储过程参数名错! procedure=" + procedure);
			return;
		}

		if (checkNull(id) == true) {
			resp.getWriter().write("id is null");
			return;
		}

		if (checkNull(name) == true) {
			resp.getWriter().write("name is null");
			return;
		}
		if (checkNull(phone) == true) {
			resp.getWriter().write("phone is null");
			return;
		}
		if (checkNull(sex) == true) {
			resp.getWriter().write("sex is null");
			return;
		}
		if (checkNull(birth_date) == true) {
			resp.getWriter().write("birth_date is null");
			return;
		}
		if (checkNull(empno) == true) {
			resp.getWriter().write("empno is null");
			return;
		}
		if (checkNull(question_str) == true) {
			resp.getWriter().write("question_str is null");
			return;
		}
		if (checkNull(version) == true) {
			resp.getWriter().write("version is null");
			return;
		}
		resp.getWriter().write(callProcedure(procedure,id,name,phone,sex,birth_date,empno,recommend_no,recommend_name,question_str,version));
		return;

	}
	
	private String callProcedure(String procedure,String id,String name,String phone,String sex,String birth_date,String empno,String recommend_no,String recommend_name,String question_str,String version){
		String driver = "oracle.jdbc.driver.OracleDriver";
	    String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
	    String result = "";
	    Statement stmt = null;
	    ResultSet rs = null;
	    Connection conn = null;
	    //CallableStatement cstmt = null;
	 
	    try {
	      Class.forName(driver);
	      conn =  DriverManager.getConnection(strUrl, "test", "test");
	      CallableStatement proc = null;
	      proc = conn.prepareCall("{ call " + procedure + "(?,?,?,?,?,?,?,?,?,?,?) }");
	      proc.setString(1, id);
	      proc.setString(2, name);
	      proc.setString(3, phone);
	      proc.setString(4, sex);
	      proc.setString(5, birth_date);
	      proc.setString(6, empno);
	      proc.setString(7, recommend_no);
	      proc.setString(8, recommend_name);
	      proc.setString(9, question_str);
	      proc.setString(10, version);
	      proc.registerOutParameter(11, java.sql.Types.VARCHAR);
	      proc.execute();
	      //从缓存中获得输出结果
	      result = proc.getString(11);
	    }
	    catch (SQLException ex2) {
	      ex2.printStackTrace();
	    }
	    catch (Exception ex2) {
	      ex2.printStackTrace();
	    }
	    finally{
	      try {
	        if(rs != null){
	          rs.close();
	          if(stmt!=null){
	            stmt.close();
	          }
	          if(conn!=null){
	            conn.close();
	          }
	        }
	      }
	      catch (SQLException ex1) {
	      }
	    }
	    return result;
	  }
		

	private boolean checkNull(String key) {
		if (key == null || key.trim().equals("") == true) {
			return true;
		}

		else {
			return false;
		}
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		this.doGet(req, resp);
	}

}
